<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2018/6/12
 * Time: 16:49
 */
namespace App\Modules\BackCost\Models;
use Illuminate\Database\Eloquent\Model;
use DB;

class HbMonthStats extends Model {
    protected $table = 'promotion_groups_hb_month_stats';

    protected $fillable = ['id','distribution_channel_id','gzh_name','appid','cost','advertise_income','sub_user_num',
        'actual_sub_num','accum_actual_sub_num','cancel_sub_num','total_income','start_time','end_time','created_at',
        'updated_at','account_recharge','outside_recharge','month','remark','accum_recharge_sum','accum_cost_sum'];

    public static function getByChannelMonth($distribution_channel_id,$month) {
        return self::where('month',$month)->where('distribution_channel_id',$distribution_channel_id)->first();
    }

    public static function getOneByAppidMonth($appid,$month) {
        return self::where('appid',$appid)->where('month',$month)->first();
    }

    public static function getMonthData($params,$is_all) {
        $obj = self::orderBy('id','desc');

        if(isset($params['appid'])&&$params['appid']) {
            $obj->where('appid',$params['appid']);
        }
        if($is_all){
            return $obj->get();
        }else{
            return $obj->paginate(20);
        }
    }

    public static function getAccountsSum($params,$is_all=false) {
        $obj = self::join('promotion_groups_hb_gzhs','promotion_groups_hb_gzhs.appid','=','promotion_groups_hb_month_stats.appid')
            ->groupBy('promotion_groups_hb_month_stats.appid')
            ->orderBy('month','desc')
            ->select(DB::raw('sum(promotion_groups_hb_month_stats.sub_user_num) as sub_user_num_sum,
            sum(promotion_groups_hb_month_stats.cancel_sub_num) as cancel_sub_num_sum,
            promotion_groups_hb_month_stats.accum_actual_sub_num,
            
            sum(promotion_groups_hb_month_stats.account_recharge) as account_recharge_sum,
            sum(promotion_groups_hb_month_stats.advertise_income) as advertise_income_sum,
            sum(promotion_groups_hb_month_stats.outside_recharge) as outside_recharge_sum,
            sum(promotion_groups_hb_month_stats.cost) as cost_sum,
            promotion_groups_hb_month_stats.gzh_name,
            promotion_groups_hb_month_stats.appid,
            promotion_groups_hb_month_stats.remark,
            promotion_groups_hb_gzhs.start_time,
            promotion_groups_hb_gzhs.end_time,
            promotion_groups_hb_gzhs.distribution_channel_id'));
        if($is_all){
            return $obj->get();
        }else{
            return $obj->paginate();
        }
    }
}